Execute Stored Procedure
AutomatR.MSSQLDataBase.Database.ExecuteStoredProcedure
The "Execute Stored Procedure" activity in AutomatR for SQL Server databases allows you to execute a stored procedure and retrieve the result set as a DataSet. This activity is useful for invoking stored procedures and obtaining data or performing actions within your automation workflow.
Properties
| Name | Description |
|---|---|
| Input | |
| Connection | Provide a SQL connection variable that will be used to establish a database connection. |
| Procedure Name | Provide the name of the stored procedure to be executed. |
| Parameters | Specify input and output parameters for the stored procedure. The parameters include the parameter name, direction (In/Out), and data type. |
| Misc | |
| DisplayName | Provides a customizable name for the activity displayed in the workflow. The display name enhances clarity and organization within the automation project. String variables containing the desired display name. |
| Optional | |
| Command Timeout | Provide the amount of time (in milliseconds) to wait for the SQL command to run before throwing an error. If not specified, the default timeout is set to 60 seconds. |
| Delay | Specifies the amount of time (in seconds) to wait before executing the "Execute Stored Procedure" activity. This can be useful for handling synchronization issues. Integer variables containing the delay duration. Ex.: If the amount of time is 1000 milliseconds or 1 sec, i.e., 1. |
| Output | |
| Result | Outputs the result of the stored procedure execution as a DataSet. The DataSet variable can be used for further activities and processing in the workflow. |
| Out Variables | Provides a Dictionary containing the values of output parameters after executing the stored procedure. This allows you to retrieve and use the values of output parameters in subsequent activities or operations. |
How to use:
- Drag and drop the "Execute Stored Procedure" activity onto the workflow.
- Configure the properties by specifying the SQL connection variable, stored procedure name, and input/output parameters.
- Optionally, configure the command timeout and delay for synchronization purposes.
- Execute the workflow to execute the specified stored procedure on the database and retrieve the result set as a DataSet.
Example: Consider an example where the "Execute Stored Procedure" activity is used to execute a stored procedure named "GetEmployeeData" with input parameters and an output parameter:
Execute Stored Procedure:
Connection: SqlConnectionVariable
Procedure Name: "GetEmployeeData"
Parameters:
- Parameter1 (In, Int32): 123
- Parameter2 (In, String): "John"
- OutputParam (Out, String): employeeName
Command Timeout: 5000
Result: EmployeeDataSet
Out Variables: OutputParameters
In this example, the activity executes the "GetEmployeeData" stored procedure with input parameters (Parameter1 and Parameter2) and an output parameter (OutputParam). The result, stored in the DataSet variable "EmployeeDataSet," and the values of output parameters in the Dictionary variable "OutputParameters" can be used for further processing in the workflow.